﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace ProcessoSeletivo.DAO
{
    public class DivulgacaoCandidato : IDisposable
    {
        #region " Listar "

        public DataView ListarDivulgacaoCandidatoPorDivulgacao(int cod_divulgacao_div)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarDivulgacaoCandidatoPorDivulgacao", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_divulgacao_div", cod_divulgacao_div);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_divulgacao_candidato");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        public DataView ListarDivulgacaoCandidatoPorCandidato(int cod_candidato_can)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarDivulgacaoCandidatoPorCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_divulgacao_candidato");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Incluir "

        public void IncluirDivulgacaoCandidato(int cod_divulgacao_div, int cod_candidato_can, string des_outras_dca)
        {            

            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("IncluirDivulgacaoCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_divulgacao_div", cod_divulgacao_div);
                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);

                if (!String.IsNullOrEmpty(des_outras_dca))
                {
                    sqlc.Parameters.AddWithValue("@des_outras_dca", des_outras_dca);
                }
                else
                {
                    sqlc.Parameters.AddWithValue("@des_outras_dca", DBNull.Value);
                }

                sqlc.ExecuteNonQuery();
                sql.Close();
            }
           
        }

        #endregion

        #region " Excluir "

        public void ExcluirDivulgacaoCandidatoPorCandidato(int cod_candidato_can)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ExcluirDivulgacaoCandidatoPorCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        #endregion

        #region " Dipose "

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
